CREATE TABLE jms_dws.dws_shipno_weight_sum
(
  `datetime`             date                 NULL   COMMENT '任务日期',
  shipmentno             varchar(50)          NULL   COMMENT '任务号',
  start_network_code     varchar(50)          NULL   COMMENT '出发网点code',
  end_network_code       varchar(50)          NULL   COMMENT '目的网点code',
  shipment_name          varchar(50)          NULL   COMMENT '任务名称',
  start_network_name     varchar(50)          NULL   COMMENT '出发网点名称',
  end_network_name       varchar(50)          NULL   COMMENT '目的网点名称',
  billing_way            int                  NULL   COMMENT '计费方式(1：整车计重、2：零担计重、3：按趟计费)',
  shipment_state         int                  NULL   COMMENT '任务状态,待调度0 已调度1 待执行2 在途3 已完成4 已作废5',
  loading_weight         decimal(10,2)        NULL   COMMENT '装车重量',
  unloading_weight       decimal(10,2)        NULL   COMMENT '卸车重量',
  planned_departure_time datetime             NULL   COMMENT '计划离开时间',
  actual_departure_time  datetime             NULL   COMMENT '实际离开时间',
  planned_arrival_time   datetime             NULL   COMMENT '计划到达时间',
  actual_arrival_time    datetime             NULL   COMMENT '实际到达时间',
  update_time            datetime             NULL   COMMENT '更新时间',
  is_delete              int                  NULL   COMMENT '是否废弃;1:正常,2:废弃'
) ENGINE = OLAP
UNIQUE KEY(`datetime`,`shipmentno`,start_network_code,`end_network_code`)
COMMENT "干线重量统计"
PARTITION BY RANGE(`datetime`)
(PARTITION p20210111 VALUES [('2021-01-11'), ('2021-01-12')),
PARTITION p20210112 VALUES [('2021-01-12'), ('2021-01-13')),
PARTITION p20210113 VALUES [('2021-01-13'), ('2021-01-14')),
PARTITION p20210114 VALUES [('2021-01-14'), ('2021-01-15')),
PARTITION p20210115 VALUES [('2021-01-15'), ('2021-01-16')),
PARTITION p20210116 VALUES [('2021-01-16'), ('2021-01-17')),
PARTITION p20210117 VALUES [('2021-01-17'), ('2021-01-18')),
PARTITION p20210118 VALUES [('2021-01-18'), ('2021-01-19')),
PARTITION p20210119 VALUES [('2021-01-19'), ('2021-01-20')),
PARTITION p20210120 VALUES [('2021-01-20'), ('2021-01-21')),
PARTITION p20210121 VALUES [('2021-01-21'), ('2021-01-22')),
PARTITION p20210122 VALUES [('2021-01-22'), ('2021-01-23')),
PARTITION p20210123 VALUES [('2021-01-23'), ('2021-01-24')),
PARTITION p20210124 VALUES [('2021-01-24'), ('2021-01-25')),
PARTITION p20210125 VALUES [('2021-01-25'), ('2021-01-26')),
PARTITION p20210126 VALUES [('2021-01-26'), ('2021-01-27')),
PARTITION p20210127 VALUES [('2021-01-27'), ('2021-01-28')),
PARTITION p20210128 VALUES [('2021-01-28'), ('2021-01-29')),
PARTITION p20210129 VALUES [('2021-01-29'), ('2021-01-30')),
PARTITION p20210130 VALUES [('2021-01-30'), ('2021-01-31')),
PARTITION p20210131 VALUES [('2021-01-31'), ('2021-02-01')),
PARTITION p20210201 VALUES [('2021-02-01'), ('2021-02-02')),
PARTITION p20210202 VALUES [('2021-02-02'), ('2021-02-03')),
PARTITION p20210203 VALUES [('2021-02-03'), ('2021-02-04')),
PARTITION p20210204 VALUES [('2021-02-04'), ('2021-02-05')),
PARTITION p20210205 VALUES [('2021-02-05'), ('2021-02-06')),
PARTITION p20210206 VALUES [('2021-02-06'), ('2021-02-07')),
PARTITION p20210207 VALUES [('2021-02-07'), ('2021-02-08')),
PARTITION p20210208 VALUES [('2021-02-08'), ('2021-02-09')),
PARTITION p20210209 VALUES [('2021-02-09'), ('2021-02-10')),
PARTITION p20210210 VALUES [('2021-02-10'), ('2021-02-11')),
PARTITION p20210211 VALUES [('2021-02-11'), ('2021-02-12')),
PARTITION p20210212 VALUES [('2021-02-12'), ('2021-02-13')),
PARTITION p20210213 VALUES [('2021-02-13'), ('2021-02-14')),
PARTITION p20210214 VALUES [('2021-02-14'), ('2021-02-15')),
PARTITION p20210215 VALUES [('2021-02-15'), ('2021-02-16')),
PARTITION p20210216 VALUES [('2021-02-16'), ('2021-02-17')),
PARTITION p20210217 VALUES [('2021-02-17'), ('2021-02-18')),
PARTITION p20210218 VALUES [('2021-02-18'), ('2021-02-19')),
PARTITION p20210219 VALUES [('2021-02-19'), ('2021-02-20')),
PARTITION p20210220 VALUES [('2021-02-20'), ('2021-02-21')),
PARTITION p20210221 VALUES [('2021-02-21'), ('2021-02-22')),
PARTITION p20210222 VALUES [('2021-02-22'), ('2021-02-23')),
PARTITION p20210223 VALUES [('2021-02-23'), ('2021-02-24')),
PARTITION p20210224 VALUES [('2021-02-24'), ('2021-02-25')),
PARTITION p20210225 VALUES [('2021-02-25'), ('2021-02-26')),
PARTITION p20210226 VALUES [('2021-02-26'), ('2021-02-27')),
PARTITION p20210227 VALUES [('2021-02-27'), ('2021-02-28')),
PARTITION p20210228 VALUES [('2021-02-28'), ('2021-03-01')),
PARTITION p20210301 VALUES [('2021-03-01'), ('2021-03-02')),
PARTITION p20210302 VALUES [('2021-03-02'), ('2021-03-03')),
PARTITION p20210303 VALUES [('2021-03-03'), ('2021-03-04')),
PARTITION p20210304 VALUES [('2021-03-04'), ('2021-03-05')),
PARTITION p20210305 VALUES [('2021-03-05'), ('2021-03-06')),
PARTITION p20210306 VALUES [('2021-03-06'), ('2021-03-07')),
PARTITION p20210307 VALUES [('2021-03-07'), ('2021-03-08')),
PARTITION p20210308 VALUES [('2021-03-08'), ('2021-03-09')),
PARTITION p20210309 VALUES [('2021-03-09'), ('2021-03-10')),
PARTITION p20210310 VALUES [('2021-03-10'), ('2021-03-11')),
PARTITION p20210311 VALUES [('2021-03-11'), ('2021-03-12')),
PARTITION p20210312 VALUES [('2021-03-12'), ('2021-03-13')),
PARTITION p20210313 VALUES [('2021-03-13'), ('2021-03-14')),
PARTITION p20210314 VALUES [('2021-03-14'), ('2021-03-15')),
PARTITION p20210315 VALUES [('2021-03-15'), ('2021-03-16')),
PARTITION p20210316 VALUES [('2021-03-16'), ('2021-03-17')),
PARTITION p20210317 VALUES [('2021-03-17'), ('2021-03-18')),
PARTITION p20210318 VALUES [('2021-03-18'), ('2021-03-19')),
PARTITION p20210319 VALUES [('2021-03-19'), ('2021-03-20')),
PARTITION p20210320 VALUES [('2021-03-20'), ('2021-03-21')),
PARTITION p20210321 VALUES [('2021-03-21'), ('2021-03-22')),
PARTITION p20210322 VALUES [('2021-03-22'), ('2021-03-23')),
PARTITION p20210323 VALUES [('2021-03-23'), ('2021-03-24')),
PARTITION p20210324 VALUES [('2021-03-24'), ('2021-03-25')),
PARTITION p20210325 VALUES [('2021-03-25'), ('2021-03-26')),
PARTITION p20210326 VALUES [('2021-03-26'), ('2021-03-27')),
PARTITION p20210327 VALUES [('2021-03-27'), ('2021-03-28')),
PARTITION p20210328 VALUES [('2021-03-28'), ('2021-03-29')),
PARTITION p20210329 VALUES [('2021-03-29'), ('2021-03-30')),
PARTITION p20210330 VALUES [('2021-03-30'), ('2021-03-31')),
PARTITION p20210331 VALUES [('2021-03-31'), ('2021-04-01')),
PARTITION p20210401 VALUES [('2021-04-01'), ('2021-04-02')),
PARTITION p20210402 VALUES [('2021-04-02'), ('2021-04-03')),
PARTITION p20210403 VALUES [('2021-04-03'), ('2021-04-04')),
PARTITION p20210404 VALUES [('2021-04-04'), ('2021-04-05')),
PARTITION p20210405 VALUES [('2021-04-05'), ('2021-04-06')),
PARTITION p20210406 VALUES [('2021-04-06'), ('2021-04-07')),
PARTITION p20210407 VALUES [('2021-04-07'), ('2021-04-08')),
PARTITION p20210408 VALUES [('2021-04-08'), ('2021-04-09')),
PARTITION p20210409 VALUES [('2021-04-09'), ('2021-04-10')),
PARTITION p20210410 VALUES [('2021-04-10'), ('2021-04-11')),
PARTITION p20210411 VALUES [('2021-04-11'), ('2021-04-12')),
PARTITION p20210412 VALUES [('2021-04-12'), ('2021-04-13')),
PARTITION p20210413 VALUES [('2021-04-13'), ('2021-04-14')),
PARTITION p20210414 VALUES [('2021-04-14'), ('2021-04-15')),
PARTITION p20210415 VALUES [('2021-04-15'), ('2021-04-16')),
PARTITION p20210416 VALUES [('2021-04-16'), ('2021-04-17')),
PARTITION p20210417 VALUES [('2021-04-17'), ('2021-04-18')),
PARTITION p20210418 VALUES [('2021-04-18'), ('2021-04-19')),
PARTITION p20210419 VALUES [('2021-04-19'), ('2021-04-20')),
PARTITION p20210420 VALUES [('2021-04-20'), ('2021-04-21')),
PARTITION p20210421 VALUES [('2021-04-21'), ('2021-04-22')),
PARTITION p20210422 VALUES [('2021-04-22'), ('2021-04-23')),
PARTITION p20210423 VALUES [('2021-04-23'), ('2021-04-24')),
PARTITION p20210424 VALUES [('2021-04-24'), ('2021-04-25')),
PARTITION p20210425 VALUES [('2021-04-25'), ('2021-04-26')),
PARTITION p20210426 VALUES [('2021-04-26'), ('2021-04-27')),
PARTITION p20210427 VALUES [('2021-04-27'), ('2021-04-28')),
PARTITION p20210428 VALUES [('2021-04-28'), ('2021-04-29')),
PARTITION p20210429 VALUES [('2021-04-29'), ('2021-04-30')),
PARTITION p20210430 VALUES [('2021-04-30'), ('2021-05-01')),
PARTITION p20210501 VALUES [('2021-05-01'), ('2021-05-02')),
PARTITION p20210502 VALUES [('2021-05-02'), ('2021-05-03')),
PARTITION p20210503 VALUES [('2021-05-03'), ('2021-05-04')),
PARTITION p20210504 VALUES [('2021-05-04'), ('2021-05-05')),
PARTITION p20210505 VALUES [('2021-05-05'), ('2021-05-06')),
PARTITION p20210506 VALUES [('2021-05-06'), ('2021-05-07')),
PARTITION p20210507 VALUES [('2021-05-07'), ('2021-05-08')),
PARTITION p20210508 VALUES [('2021-05-08'), ('2021-05-09')),
PARTITION p20210509 VALUES [('2021-05-09'), ('2021-05-10')),
PARTITION p20210510 VALUES [('2021-05-10'), ('2021-05-11')),
PARTITION p20210511 VALUES [('2021-05-11'), ('2021-05-12')),
PARTITION p20210512 VALUES [('2021-05-12'), ('2021-05-13')),
PARTITION p20210513 VALUES [('2021-05-13'), ('2021-05-14')),
PARTITION p20210514 VALUES [('2021-05-14'), ('2021-05-15')),
PARTITION p20210515 VALUES [('2021-05-15'), ('2021-05-16')),
PARTITION p20210516 VALUES [('2021-05-16'), ('2021-05-17')),
PARTITION p20210517 VALUES [('2021-05-17'), ('2021-05-18')),
PARTITION p20210518 VALUES [('2021-05-18'), ('2021-05-19')),
PARTITION p20210519 VALUES [('2021-05-19'), ('2021-05-20')),
PARTITION p20210520 VALUES [('2021-05-20'), ('2021-05-21')),
PARTITION p20210521 VALUES [('2021-05-21'), ('2021-05-22')),
PARTITION p20210522 VALUES [('2021-05-22'), ('2021-05-23')),
PARTITION p20210523 VALUES [('2021-05-23'), ('2021-05-24')),
PARTITION p20210524 VALUES [('2021-05-24'), ('2021-05-25')),
PARTITION p20210525 VALUES [('2021-05-25'), ('2021-05-26')),
PARTITION p20210526 VALUES [('2021-05-26'), ('2021-05-27')),
PARTITION p20210527 VALUES [('2021-05-27'), ('2021-05-28')),
PARTITION p20210528 VALUES [('2021-05-28'), ('2021-05-29')),
PARTITION p20210529 VALUES [('2021-05-29'), ('2021-05-30')),
PARTITION p20210530 VALUES [('2021-05-30'), ('2021-05-31')),
PARTITION p20210531 VALUES [('2021-05-31'), ('2021-06-01')),
PARTITION p20210601 VALUES [('2021-06-01'), ('2021-06-02')),
PARTITION p20210602 VALUES [('2021-06-02'), ('2021-06-03')),
PARTITION p20210603 VALUES [('2021-06-03'), ('2021-06-04')),
PARTITION p20210604 VALUES [('2021-06-04'), ('2021-06-05')),
PARTITION p20210605 VALUES [('2021-06-05'), ('2021-06-06')),
PARTITION p20210606 VALUES [('2021-06-06'), ('2021-06-07')),
PARTITION p20210607 VALUES [('2021-06-07'), ('2021-06-08')),
PARTITION p20210608 VALUES [('2021-06-08'), ('2021-06-09')),
PARTITION p20210609 VALUES [('2021-06-09'), ('2021-06-10')),
PARTITION p20210610 VALUES [('2021-06-10'), ('2021-06-11')),
PARTITION p20210611 VALUES [('2021-06-11'), ('2021-06-12')),
PARTITION p20210612 VALUES [('2021-06-12'), ('2021-06-13')),
PARTITION p20210613 VALUES [('2021-06-13'), ('2021-06-14')),
PARTITION p20210614 VALUES [('2021-06-14'), ('2021-06-15')),
PARTITION p20210615 VALUES [('2021-06-15'), ('2021-06-16')),
PARTITION p20210616 VALUES [('2021-06-16'), ('2021-06-17')),
PARTITION p20210617 VALUES [('2021-06-17'), ('2021-06-18')),
PARTITION p20210618 VALUES [('2021-06-18'), ('2021-06-19')),
PARTITION p20210619 VALUES [('2021-06-19'), ('2021-06-20')),
PARTITION p20210620 VALUES [('2021-06-20'), ('2021-06-21')),
PARTITION p20210621 VALUES [('2021-06-21'), ('2021-06-22')),
PARTITION p20210622 VALUES [('2021-06-22'), ('2021-06-23')),
PARTITION p20210623 VALUES [('2021-06-23'), ('2021-06-24')),
PARTITION p20210624 VALUES [('2021-06-24'), ('2021-06-25')),
PARTITION p20210625 VALUES [('2021-06-25'), ('2021-06-26')),
PARTITION p20210626 VALUES [('2021-06-26'), ('2021-06-27')),
PARTITION p20210627 VALUES [('2021-06-27'), ('2021-06-28')),
PARTITION p20210628 VALUES [('2021-06-28'), ('2021-06-29')),
PARTITION p20210629 VALUES [('2021-06-29'), ('2021-06-30')),
PARTITION p20210630 VALUES [('2021-06-30'), ('2021-07-01')),
PARTITION p20210701 VALUES [('2021-07-01'), ('2021-07-02')),
PARTITION p20210702 VALUES [('2021-07-02'), ('2021-07-03')),
PARTITION p20210703 VALUES [('2021-07-03'), ('2021-07-04')),
PARTITION p20210704 VALUES [('2021-07-04'), ('2021-07-05')),
PARTITION p20210705 VALUES [('2021-07-05'), ('2021-07-06')),
PARTITION p20210706 VALUES [('2021-07-06'), ('2021-07-07')),
PARTITION p20210707 VALUES [('2021-07-07'), ('2021-07-08')),
PARTITION p20210708 VALUES [('2021-07-08'), ('2021-07-09')),
PARTITION p20210709 VALUES [('2021-07-09'), ('2021-07-10')))
DISTRIBUTED BY HASH(`shipmentno`) BUCKETS 10
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "10",
"in_memory" = "false",
"storage_format" = "v2"
);



ALTER TABLE jms_dws.dws_shipno_weight_sum ADD COLUMN (
    loading_package int COMMENT '装车包数',
    unloading_package int COMMENT '卸车包数'
);


ALTER TABLE jms_dws.dws_shipno_weight_sum ADD COLUMN (
    rough_unloading_weight decimal(10,2) COMMENT '粗判重量'
);

ALTER TABLE jms_dws.dws_shipno_weight_sum ADD COLUMN (
    vehicletype_id varchar(100) COMMENT '车型ID',
    vehicletype_name varchar(100) COMMENT '车型名称',
    settlement_vehicle_type_id varchar(100) COMMENT '结算车型ID',
    settlement_vehicle_type_name varchar(100) COMMENT '结算车型名称'
);